package com.atguigu.jdbc;

import org.junit.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * Date:2020/8/28
 * Author:ybc
 * Description:
 */
public class BatchHandlerTest {

    /**
     * 批处理的第一种写法：
     *  每次循环都执行一次SQL语句
     */
    @Test
    public void testBatch(){
        Connection connection = null;
        PreparedStatement ps = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","123456");
            ps = connection.prepareStatement("insert into t_user(username,password) values(?,?)");
            for (int i = 1; i <= 1000; i++) {
                ps.setObject(1,"user"+i);
                ps.setObject(2,"pwd"+i);
                ps.executeUpdate();
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                ps.close();
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 批处理的第二种方式：
     *  addBatch()：将当前的批处理操作攒为一批进行操作
     *  executeBatch()：执行一批sql语句
     *  mysql的同步操作：
     *      在连接地址的后面通过?添加参数：rewriteBatchedStatements=true
     *      通过此参数，可以设置mysql的基础配置，比如rewriteBatchedStatements=true，用来开启mysql的同步操作
     *      若有多个参数，统一资源定位符?参数名=参数值&参数名=参数值
     */
    @Test
    public void testBatchNew(){
        //获取系统当前时间所对应的时间戳（毫秒数）
        long start = System.currentTimeMillis();
        Connection connection = null;
        PreparedStatement ps = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true","root","123456");
            ps = connection.prepareStatement("insert into t_user(username,password) values(?,?)");
            for (int i = 1; i <= 1000; i++) {
                ps.setObject(1,"user"+i);
                ps.setObject(2,"pwd"+i);
                //将sql攒为一批进行处理
                ps.addBatch();
            }
            //执行这一批sql
            ps.executeBatch();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                ps.close();
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        long end = System.currentTimeMillis();
        System.out.println("耗时："+(end-start));
    }

}
